
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_dm.dm_taking_timely_mall_detail_dt
select
    taking.waybill_no             --运单号'
   ,taking.order_no
   ,taking.mall_id                --商户ID
   ,taking.mall_name              --商户名称
   ,taking.customer_code          --客户编码
   ,taking.customer_name          --客户名称
   ,taking.input_time             --订单录入时间
   ,taking.taking_time            --揽收时间
   ,taking.pick_end_time          --取件结束时间
   ,taking.start_network_code     --始发网点编码
   ,taking.start_network_name     --始发网点名称
   ,taking.start_city_code        --始发市编码
   ,taking.start_city_name        --始发市名称
   ,taking.start_province_code    --始发省编码
   ,taking.start_province_name    --始发省名称
   ,taking.start_agent_code       --始发代理区编码
   ,taking.start_agent_name       --始发代理区名称
   ,way.end_network_code          --目的网点编码
   ,way.end_network_name          --目的网点名称
   ,way.end_city_code             --目的市编码
   ,way.end_city_name             --目的市名称
   ,way.end_provider_code         --目的省编码
   ,way.end_provider_name         --目的省名称
   ,way.end_agent_code            --目的代理区编码
   ,way.end_agent_name            --目的代理区名称
   ,taking.if_tml_collect            --是否及时揽收
   ,to_date(taking.pick_end_time) as dt --分区日期
from (
    select
         taking.waybill_no      --运单号'
        ,taking.order_no        --订单号
        ,taking.customer_code   --客户编码'
        ,taking.customer_name   --客户名称'
        ,taking.input_time      --订单录入时间'
        ,taking.taking_time     --揽收时间'
        ,taking.pick_end_time   --取件结束时间'
        ,taking.sender_network_code  as start_network_code     --寄件网点编码'
        ,taking.sender_network_name  as start_network_name     --寄件网点名称'
        ,network_detail.city_id     as start_city_code        --寄件市编码'
        ,network_detail.city_desc     as start_city_name        --寄件市名称'
        ,network_detail.provider_id as start_province_code    --寄件省编码'
        ,network_detail.provider_desc as start_province_name    --寄件省名称'
        ,network_detail.agent_code    as start_agent_code       --寄件代理区编码'
        ,network_detail.agent_name    as start_agent_name       --寄件代理区名称'
        ,taking.if_tml_collect
        ,cust.mall_id                  --商户ID
        ,cust.mall_name                --商户名称
    from jms_dm.dm_taking_timely_detail_hi taking
        left join (
    select code,
           name,
           city_id,
           city_desc,
           provider_id,
           provider_desc,
           agent_code,
           agent_name
    from jms_dim.dim_network_whole_massage
    where is_delete=1
    and is_enable=1
    ) network_detail
        on taking.sender_network_code=network_detail.code
    left join jms_dim.dim_lmdm_sys_customer cust
       on taking.customer_code = cust.code
     join (
        select *
        from (
            select
                    waybill_id as waybill_no
                -- ,row_number() over(partition by waybill_id order by update_time desc) as rn
            from jms_dwd.dwd_yl_oms_order_mark_expand_base_dt
            where dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
            and field_name='YF_SVC-ASSUREMENT'
        ) a
    ) mark on mark.waybill_no = taking.waybill_no
    where taking.dt between date_sub('{{ execution_date | cst_ds }}',1) and '{{ execution_date | cst_ds }}'
    and to_date(taking.pick_end_time) = '{{ execution_date | cst_ds }}'
    and mark.waybill_no is not null
) taking
left join (
    select
         way.waybill_no
        ,way.dispatch_network_code
        ,dim.code           as end_network_code
        ,dim.name           as end_network_name
        ,dim.city_id        as end_city_code
        ,dim.city_desc      as end_city_name
        ,dim.provider_id    as end_provider_code
        ,dim.provider_desc  as end_provider_name
        ,dim.agent_code     as end_agent_code
        ,dim.agent_name     as end_agent_name
    from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt way
    left join jms_dim.dim_network_whole_massage dim
        on way.dispatch_network_code = dim.code
    where way.dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
) way on taking.waybill_no = way.waybill_no
distribute by pmod(hash(rand()),1);